<?php
/*
 * (C) Copyright 2013 Torsten Gorath (http://www.blauesdach.de/).
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * Contributors:
 *     Torsten Gorath
 */
require_once $basedir."/inc/table.php";

class KontoBuchung extends table {
    
    public static $TABLENAME = "konto_buchung";
    public static $CLASSNAME = "KontoBuchung";
    
    private $id = "";
    private $konto_id = "";
    private $buchungs_datum = "";
    private $werstellungs_datum = "";
    private $betrag = "";
    private $waehrung = "";
    private $buchungart_id = "";
    private $buchungsart = "";
    private $verwendungszweck = "";
    private $name_gegenkonto = "";
    private $stadt_gegenkonto = "";
    private $blz_gegenkonto = "";
    private $nr_gegenkonto = "";
    private $iban_gegenkonto = "";
    private $bic_gegenkonto = "";
    
    /**
     * Constructor
     * 
     * @param int $id optional the id of the element to open from database.
     *                if not set an empty element will be created.
     */
    public function __construct($id="") {
        if($id!='') {
            $temp = Database::getDefault()->openId($TABLENAME,$id);
            if($temp!=null) {
                foreach($temp as $key => $value) {
                    $this->$key = $value;
                }
            }
        }
    }
    
    public function delete() {
        Database::getDefault()->delete($TABLENAME,$this->id);
    }
    
    public function save() {
        $values = array();
        foreach ($this as $key => $value) {
            $values[$key] = $value;
        }
        if($this->id=="") {
            $this->id = Database::getDefault()->insert($TABELNAME, $values);
        } else {
            Database::getDefault()->update($TABELNAME, $values, $this->id);
        }
    }
    
    /**
     * Get all groups
     */
    static public function getAll() {
        global $VEREIN;
        $query = "SELECT * FROM " . $VEREIN['prefix'] . $TABELNAME . " ORDER BY wertstellungs_datum";
        return Database::getDefault()->execute($query,$CLASSNAME);
    }

    public function getId() {
        return $this->id;
    }
    
    public function getKontoId() {
        return $this->konto_id;
    }
    
    public function getBuchungsDatum() {
        return $this->buchungs_datum;
    }
    
    public function getWertstellungsDatum() {
        return $this->werstellungs_datum;
    }
    
    public function getBetrag() {
        return $this->betrag;
    }
    
    public function getWaehrung() {
        return $this->waehrung;
    }
    
    public function getBuchungsartId() {
        return $this->buchungart_id;
    }
    
    public function getBuchungsart() {
        return $this->buchungsart;
    }

    public function getVerwendungszweck() {
        return $this->verwendungszweck;
    }
    
    public function getGegenkontoname() {
        return $this->name_gegenkonto;
    }
    
    public function getGegenkontoStadt() {
        return $this->stadt_gegenkonto;
    }

    public function getGegenkontoBlz() {
        return $this->blz_gegenkonto;
    }
    
    public function getGegenkontoNumber() {
        return $this->nr_gegenkonto;
    }
    
    public function getGegenkontoIban() {
        return $this->iban_gegenkonto;
    }
    
    public function getGegenkontoBic() {
        return $this->bic_gegenkonto;
    }
    
    public static function importFile($filename) {
        $row = 1;
        if(($handle = fopen($filename, "r")) !== FALSE) {
            while (($data = fgetcsv($handle, 0, ";")) !== FALSE) {
                $num = count($data);
                if($num <=1) {
                    continue;
                }
                $kontonr = "";
                $blz = "";
                $this->verwendungszweck = "";
                for($c=0; $c<$num; $c++) {
                    switch ($c) {
                        case 1:
                            $blz = $data[$c];
                            break;
                        case 2:
                            $kontonr = $data[$c];
                            if($blz != "" && $kontonr != "") {
                                $konto = new Konto("", $blz, $kontonr);
                                $this->konto_id = $konto->getId();
                            }
                            break;
                        case 3:
                            $this->buchungs_datum = strftime("Y-m-d", fdate_parse_from_format("j.n.Y", $data[$c]));
                            break;
                        case 4:
                            $this->werstellungs_datum = strftime("Y-m-d", fdate_parse_from_format("j.n.Y", $data[$c]));
                            break;
                        case 5:
                            $this->betrag = floatval($data[$c]);
                            break;
                        case 6:
                            $this->waehrung = $data[$c];
                            break;
                        case 7:
                            $this->buchungart_id = $data[$c];
                            break;
                        case 8:
                            $this->buchungart = $data[$c];
                            break;
                        case $c >= 9 && $c<=22:
                            $this->verwendungszweck .= $data[$c];
                            break;
                        case 23:
                            $this->name_gegenkonto = $data[$c];
                            break;
                        case 24:
                            $this->stadt_gegenkonto = $data[$c];
                            break;
                        case 25:
                            $this->blz_gegenkonto = $data[$c];
                            break;
                        case 25:
                            $this->nr_gegenkonto = $data[$c];
                            break;
                        default:
                            
                            break;
                    }
                }
                $this->save();
            }
            fclose($handle);
        }
    }
    
    /**
     * create table for addresses or update if something to do
     */
    public static function initDatabase() {
        global $VEREIN;
        echo "<b>create / update " . KontoBuchung::$TABLENAME . "</b><br />";
        $query = "CREATE TABLE IF NOT EXISTS " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " (id VARCHAR(20) NOT NULL PRIMARY KEY)";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            die(Database::getDefault()->getConnection()->error);
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN konto_id INT UNSIGNED NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN buchungs_datum DATE NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN wertstellungs_datum DATE NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN betrag DECIMAL NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN waehrung VARCHAR(3) NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN buchungsart_id INT UNSIGNED NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN buchungsart VARCHAR(50) NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN verwendungszweck VARCHAR(500) NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN name_gegenkonto VARCHAR(100) NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN stadt_gegenkonto VARCHAR(100) NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN blz_gegenkonto VARCHAR(8) NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN nr_gegenkonto VARCHAR(10) NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN iban_gegenkonto VARCHAR(22) NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . KontoBuchung::$TABLENAME . " ADD COLUMN bic_gegenkonto VARCHAR(11) NOT NULL";
        echo "- " . $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        echo "<br />";
    }

}
?>